package com.qf.studentmanager.dao;

import com.mchange.v2.c3p0.ComboPooledDataSource;
import com.qf.studentmanager.config.DBConfig;
import com.qf.studentmanager.model.Clazz;
import com.qf.studentmanager.model.Grade;
import com.qf.studentmanager.model.Student;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class StudentDao {
    ComboPooledDataSource comboPooledDataSource = new ComboPooledDataSource();
    QueryRunner queryRunner = new QueryRunner(comboPooledDataSource);

    public Long getTotal(String gid, String cid) {
        StringBuffer stringBuffer = new StringBuffer("select count(1) from student s ");
        if (gid != null && cid == null){
            stringBuffer.append("where s.gid = ?");
        }else if (gid != null && cid != null){
            stringBuffer.append("where s.gid = ? and s.cid = ?");
        }

        try {
            if (gid != null && cid == null){
                return  queryRunner.query(stringBuffer.toString(), new ScalarHandler<>() , gid);
            }else if (gid != null && cid != null){
                return  queryRunner.query(stringBuffer.toString(), new ScalarHandler<>(),gid , cid);
            }else {
                return queryRunner.query(stringBuffer.toString(), new ScalarHandler<>());
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0L;
    }


    //select * from student s where  s.gid = ? and s.cid =? order by  s.sid asc limit ? , ?  gid != null && cid != null
    //select * from student s where  s.gid = ?  order by  s.sid asc limit ? , ?  gid != null && cid == null
    //select * from student s order by  s.sid asc limit ? , ?
    public List<Student> getClazzByPage(int p, int r, String sort, String order, String gid, String cid) {
        String sql = null;
        //准备好sql
        if (gid == null && cid != null){
            sql = "select s.* , g.gradeName as gradeName , c.clazzName as clazzName from student s left join clazz c on s.cid = c.cid left join grade g on s.gid = g.gid where   s.cid =?  order by  "+ sort + " " + order+"   limit ? , ?; ";
        }else if (gid != null && cid == null){
           sql = "select s.* , g.gradeName as gradeName , c.clazzName as clazzName from student s left join clazz c on s.cid = c.cid left join grade g on s.gid = g.gid where  s.gid = ?   order by  "+ sort + " " + order+"   limit ? , ?; " ;
        }else {
          sql = "select s.* , g.gradeName as gradeName , c.clazzName as clazzName from student s left join clazz c on s.cid = c.cid left join grade g on s.gid = g.gid   order  by "+ sort + " " + order+"  limit ? , ?;";
        }

        try {
            ResultSetHandler<List<Student>> rsh = new ResultSetHandler<List<Student>>() {
                @Override
                public List<Student> handle(ResultSet resultSet) throws SQLException {
                    List<Student> list = new ArrayList<>();
                     while (resultSet.next()){
                         int sid = resultSet.getInt("sid");
                         String number = resultSet.getString("number");
                         String name = resultSet.getString("name");
                         String gender = resultSet.getString("gender");
                         String phone = resultSet.getString("phone");
                         String qq = resultSet.getString("qq");

                         Grade grade = new Grade();
                         String gradeName = resultSet.getString("gradeName");
                         int gid1 = resultSet.getInt("gid");
                         grade.setGid(gid1);
                         grade.setGradeName(gradeName);

                         Clazz clazz = new Clazz();
                         int cid1 = resultSet.getInt("cid");
                         String clazzName = resultSet.getString("clazzName");
                         clazz.setCid(cid1);
                         clazz.setClazzName(clazzName);

                         Student student = new Student(sid,number,name,gender,phone,qq,cid1,gid1,clazz,grade);
                         list.add(student);

                     }
                    return list;
                }
            };

            if (gid == null && (cid != null&& !"".equals(cid))){
              return  queryRunner.query(sql, rsh ,cid , p , r ) ;
            }else if (gid != null && !"".equals(gid)  && ( cid == null || "".equals(cid))){
                return queryRunner.query(sql, rsh ,gid  , p , r ) ;
            }else {
                return queryRunner.query(sql, rsh  , p , r ) ;
            }


        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    //根据numbet查学生
    public Student getStudentByNumber(String number) {
        try {
            return queryRunner.query("select * from student s  where s.number = ?;", new BeanHandler<>(Student.class),number);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    public Integer addStudent(Student student) throws SQLException {
        Connection connection = DBConfig.getConnection();
        return queryRunner.update(connection,"insert into student( number, name, gender, phone, qq, cid, gid) values (?,?,?,?,?,?,?);", student.getNumber(),student.getName(),student.getGender(),student.getPhone(),student.getQq(),student.getCid(),student.getGid());

    }

    public Integer deleteStudentBySids(String[] sids) {
        try {
            for (String sid : sids) {
                 queryRunner.update("delete from student where sid = ?;", sid);
            }
            return 1;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return -1;
    }

    public Integer deleteStudentByNumber(String Number) throws SQLException {
        Connection connection = DBConfig.getConnection();
            return     queryRunner.update(connection,"delete from student where number = ?;", Number);
    }

}
